CREATE OR REPLACE PACKAGE BODY pkg_db_tools
IS

  FUNCTION f_db_name
  RETURN VARCHAR2
  IS
     ret_val VARCHAR2( 4000 ) := NULL;
    BEGIN
  
    SELECT *
    INTO ret_val
    FROM GLOBAL_NAME;
    
    RETURN ret_val;
  
  EXCEPTION 
     WHEN OTHERS THEN
        RETURN 'ERROR  - F_DB_NAME - '||SQLCODE;
  END f_db_name;
  
  --RETURNS DATABASE COLUMN LENGTH 
  FUNCTION f_get_column_rec
    ( the_owner IN VARCHAR2
    , the_table IN VARCHAR2
    , the_column_name IN VARCHAR2 )
  RETURN all_tab_columns%ROWTYPE
  IS
    ret_val all_tab_columns%ROWTYPE;
  BEGIN
  
    SELECT *
    INTO ret_val  
    FROM all_tab_columns
    WHERE owner = the_owner
    AND table_name = the_table
    AND column_name = the_column_name;
    
    RETURN ret_val;
    
  EXCEPTION
    WHEN OTHERS THEN
      ret_val := NULL;
      RETURN ret_val;
  END f_get_column_rec;
  
      PROCEDURE reset_sequence 
       ( seq_name IN VARCHAR2
      , startvalue IN PLS_INTEGER
      , the_response OUT VARCHAR2 ) 
    IS
    
       cval   INTEGER;
       inc_by VARCHAR2(25);
    
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
    
      EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual'
      INTO cval;
    
      cval := cval - startvalue + 1;
      IF cval < 0 THEN
        inc_by := ' INCREMENT BY ';
        cval:= ABS(cval);
      ELSE
        inc_by := ' INCREMENT BY -';
      END IF;
       
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by ||
      cval;
    
      EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual'
      INTO cval;
    
      EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name ||
      ' INCREMENT BY 1';
      
      the_response := 'SUCCESS - '||seq_name||' was reset';
    
    EXCEPTION
       WHEN OTHERS THEN
         the_response := 'FAILURE - There was a problem resetting - '||seq_name||' - '||SQLERRM; 
    END reset_sequence;
    
    /*  
    This procedure will dbms_output the proper code to grant 
        -select, insert, update, delete on all tables and views
        -execute on all procedures
    */
    PROCEDURE schema_grants
       ( the_role_name IN VARCHAR2
      , the_schema_name IN VARCHAR2 )
    IS
    
      CURSOR CUR_USER_OBJECTS( theOwner VARCHAR2 )
      IS
        SELECT OWNER, OBJECT_TYPE, OBJECT_NAME 
        FROM ALL_OBJECTS
        WHERE OWNER = theOwner
        ORDER BY OBJECT_TYPE;
        
    BEGIN
    
       FOR i IN cur_user_objects( the_schema_name ) LOOP
         IF i.OBJECT_TYPE IN ( 'FUNCTION', 'PROCEDURE', 'PACKAGE' ) THEN
           DBMS_OUTPUT.PUT_LINE( 'GRANT EXECUTE ON '||i.OWNER||'.'||i.OBJECT_NAME||' TO '||the_role_name||';' );
--        ELSIF i.OBJECT_TYPE IN ( 'TABLE' ) THEN
--           DBMS_OUTPUT.PUT_LINE( 'GRANT SELECT, INSERT, UPDATE, DELETE ON '||i.OWNER||'.'||i.OBJECT_NAME||' TO '||the_role_name||';' );
        ELSIF i.OBJECT_TYPE IN ( 'SEQUENCE', 'VIEW' ) THEN
           DBMS_OUTPUT.PUT_LINE( 'GRANT SELECT ON '||i.OWNER||'.'||i.OBJECT_NAME||' TO '||the_role_name||';' );
        ELSE
           NULL;
        END IF;
      END LOOP;
    EXCEPTION
       WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE( SQLERRM ); 
      END schema_grants;         
      
      /*
      This procedure will dbms_output the proper code to drop and recreate
      synonyms for all functions,  procedures and packages.
      */ 
      PROCEDURE schema_synonyms( the_schema_name IN VARCHAR2 )
      IS
      
         CURSOR CUR_USER_OBJECTS( theOwner VARCHAR2 )
         IS
            SELECT OWNER, OBJECT_TYPE, OBJECT_NAME 
            FROM ALL_OBJECTS
            WHERE OWNER = theOwner
            ORDER BY OBJECT_TYPE;
            
      BEGIN
      
         FOR i IN cur_user_objects( the_schema_name ) LOOP
            IF i.OBJECT_TYPE IN ( 'FUNCTION', 'PROCEDURE', 'PACKAGE' ) THEN
               DBMS_OUTPUT.PUT_LINE( 'BEGIN EXECUTE IMMEDIATE ''DROP PUBLIC SYNONYM '||i.OBJECT_NAME||'''; EXCEPTION WHEN OTHERS THEN NULL; END;' );
               DBMS_OUTPUT.PUT_LINE( 'CREATE PUBLIC SYNONYM '||i.OBJECT_NAME||' FOR '||i.OWNER||'.'||i.OBJECT_NAME||';' );
            END IF;     
         END LOOP;
                     
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE( SQLERRM );
      END schema_synonyms;    
      
END pkg_db_tools;    

/
